from mailcap import subst

import pandas as pd
from tabulate import tabulate


class ExcelDataLoader:
    def __init__(self, file_path):
        self.file_path = file_path
    # 加载数据
    # sheet_name: 指定要加载的工作表名称
    def load_data(self, sheet_name=None):
        # 加载数据时不使用表头
        data = pd.read_excel(self.file_path, header=None, sheet_name=sheet_name)
        return data
    # 提取数据
    # data: 要提取的数据
    # start_row: 开始行
    # end_row: 结束行
    # start_col: 开始列
    def extract_data_by_rows(self, data, start_row, end_row, start_col=0):
        # Extract data by rows and starting column
        extracted_data = data.iloc[start_row:end_row, start_col:]
        return extracted_data
    # 提取数据
    # data: 要提取的数据
    # sheet_name: 工作表名称
    def get_data(self,sheet_name=None):
        data = self.load_data(sheet_name)
        group1 = self.extract_data_by_rows(data, 3, 12,2)

        # 删除第4、5、7行
        group1 = group1.drop([4, 5, 7])
        # 删除除第一行之外的所有值为 0 或 NaN 的列
        df_cleaned1 = group1.loc[:, ~(group1.iloc[1:].isna() | (group1.iloc[1:] == 0)).all()]
        # 选取除了最后一列外的所有列
        df_cleaned1 = df_cleaned1.iloc[:, :-1]

        group2 = self.extract_data_by_rows(data,14,23,2)
        group2 = group2.drop([15,16,18])
        df_cleaned2 = group2.loc[:, ~(group2.iloc[1:].isna() | (group2.iloc[1:] == 0)).all()]
        df_cleaned2 = df_cleaned2.iloc[:,:-1]

        group3 = self.extract_data_by_rows(data,25,34,2)
        group3 = group3.drop([26,27,29])
        df_cleaned3 = group3.loc[:, ~(group3.iloc[1:].isna() | (group3.iloc[1:] == 0)).all()]
        df_cleaned3 = df_cleaned3.iloc[:,:-1]

        # 重置索引以忽略原始行号
        df_cleaned1.reset_index(drop=True, inplace=True)
        df_cleaned2.reset_index(drop=True, inplace=True)
        df_cleaned3.reset_index(drop=True, inplace=True)
        # 水平连接三个数据框
        df_result =  pd.concat([df_cleaned1, df_cleaned2,df_cleaned3], axis=1)
        df_result.columns = df_result.iloc[0]
        df_result = df_result[1:]
        print(tabulate(df_result, headers='keys', tablefmt='psql'))
        df_result.iloc[1], df_result.iloc[2] = df_result.iloc[2].copy(), df_result.iloc[1].copy()
        return df_result


if __name__ == '__main__':
#    pd.set_option('display.max_columns', None)  # 显示所有列
    loader = ExcelDataLoader("C:/Users/张旭东/Downloads/进矿报表.xlsx")
    data = loader.load_data("11日")
    # 提取第1列的1行到10行作为一组数据
    group1 = loader.get_data("11日")

    print("Group 1:")
    print(tabulate(group1, headers='keys', tablefmt='psql'))

